package com.example.clxy.service.impl;

import com.example.clxy.dao.*;
import com.example.clxy.entity.*;
import com.example.clxy.result.PageResult;
import com.example.clxy.result.WebRestResult;
import com.example.clxy.result.business.PageResult.*;
import com.example.clxy.service.FileService;
import com.example.clxy.service.ScoreService;
import com.example.clxy.util.FileUtil;
import com.example.clxy.util.RespUtil;
import com.example.clxy.util.UUIDUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

/**
 * @author weichong
 * date 2020-08-07
 * @Description:
 */
@Service
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public class FileServiceImpl implements FileService {

    private static final Logger log = Logger.getLogger("");

    @Autowired
    private ScoreService scoreService;

    @Autowired
    private ScheduleStudentGxMapper scheduleStudentGxMapper;
    @Autowired
    private ExperimentMapper experimentMapper;
    @Autowired
    private WlclassMapper wlclassMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private SycourseMapper sycourseMapper;
    @Autowired
    private CourseMapper courseMapper;
    @Autowired
    private QuestionMapper questionMapper;
    @Autowired
    private PlatformMapper platformMapper;
    @Autowired
    private UserPlusMapper userPlusMapper;
    @Autowired
    private SchoolMapper schoolMapper;


    @Override
    public WebRestResult upload(MultipartFile file,String filepath,String filename){
        try {
            if (file.isEmpty()) {
                return RespUtil.failResp("文件不能为空");
            }
            String OriginalFilename = file.getOriginalFilename();
            String suffixname = OriginalFilename.substring(OriginalFilename.lastIndexOf("."));
            if(!filepath.substring(filepath.length()-1).equals("\\")){
                filepath += "\\";
            }
            String path = filepath + filename;
            log.info("上传文件名:" + OriginalFilename + ",文件后缀为:" + suffixname);
            log.info("上传文件路径:" + path );

            String replacePath = path.replace('\\', '/');
            File dest = new File(replacePath);
            if (!dest.getParentFile().exists()) {
                dest.getParentFile().mkdirs();
            }
            file.transferTo(dest);
            return RespUtil.successResp("上传成功");
        }catch (IllegalStateException e){
            e.printStackTrace();
        }catch (IOException e){
            e.printStackTrace();
        }
        return RespUtil.failResp("上传失败");
    }
//    /** 获取 target/classes/static目录的绝对路径 **/
//////    @Override
//////    public String getPath(){
//////        try {
//////            String path = ResourceUtils.getURL("classpath:").getPath() + "static/";
//////            return path.replace('/', '\\').substring(1, path.length());
//////        }catch (Exception e){
//////            e.printStackTrace();
//////        }
//////        return null;
//////    }

    @Override
    public WebRestResult uploadStudentYxreport(MultipartFile file,String scheduleid,String studentid) {
        String staticPath = scheduleStudentGxMapper.getStudentPath(scheduleid,studentid);
        if(staticPath == null){
            return RespUtil.failResp("学生该排课不存在");
        }
        String filepath = FileUtil.getAbsolutePath(staticPath);
        String filename = "yxreport"+file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if( upload(file,filepath,filename).getResult()== WebRestResult.SUCCESS){
            ScheduleStudentGxKey scheduleStudentGxKey = new ScheduleStudentGxKey(scheduleid,studentid);
            ScheduleStudentGx scheduleStudentGx = scheduleStudentGxMapper.selectByPrimaryKey(scheduleStudentGxKey);
            scheduleStudentGx.setYxreport(FileUtil.FilePathUrl+staticPath.replace('\\', '/')+filename);
            scheduleStudentGx.setYxreporttime(new Date());
            if(scheduleStudentGxMapper.updateByPrimaryKeySelective(scheduleStudentGx) == 1){
                return RespUtil.successResp("上传预习报告成功");
            }
        }
        return RespUtil.failResp("上传预习报告失败");
    }

    @Override
    public WebRestResult uploadStudentSycard(MultipartFile file,String scheduleid,String studentid){
        String staticPath = scheduleStudentGxMapper.getStudentPath(scheduleid,studentid);
        if(staticPath == null){
            return RespUtil.failResp("学生该排课不存在");
        }
        String filepath = FileUtil.getAbsolutePath(staticPath);
        String filename = "sycard"+file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if( upload(file,filepath,filename).getResult()== WebRestResult.SUCCESS){
            ScheduleStudentGxKey scheduleStudentGxKey = new ScheduleStudentGxKey(scheduleid,studentid);
            ScheduleStudentGx scheduleStudentGx = scheduleStudentGxMapper.selectByPrimaryKey(scheduleStudentGxKey);
            scheduleStudentGx.setSycard(FileUtil.FilePathUrl+staticPath.replace('\\', '/')+filename);
            scheduleStudentGx.setSycardtime(new Date());
            if(scheduleStudentGxMapper.updateByPrimaryKeySelective(scheduleStudentGx) == 1){
                return RespUtil.successResp("上传实验卡成功");
            }
        }
        return RespUtil.failResp("上传实验卡失败");
    }

    @Override
    public WebRestResult uploadStudentSyreport(MultipartFile file,String scheduleid,String studentid){
        String staticPath = scheduleStudentGxMapper.getStudentPath(scheduleid,studentid);
        if(staticPath == null){
            return RespUtil.failResp("学生该排课不存在");
        }
        String filepath = FileUtil.getAbsolutePath(staticPath);
        String filename = "syreport"+file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if( upload(file,filepath,filename).getResult()== WebRestResult.SUCCESS){
            ScheduleStudentGxKey scheduleStudentGxKey = new ScheduleStudentGxKey(scheduleid,studentid);
            ScheduleStudentGx scheduleStudentGx = scheduleStudentGxMapper.selectByPrimaryKey(scheduleStudentGxKey);
            scheduleStudentGx.setSyreport(FileUtil.FilePathUrl+staticPath.replace('\\', '/')+filename);
            scheduleStudentGx.setSyreporttime(new Date());
            if(scheduleStudentGxMapper.updateByPrimaryKeySelective(scheduleStudentGx) == 1){
                return RespUtil.successResp("上传实验报告成功");
            }
        }
        return RespUtil.failResp("上传实验报告失败");
    }

    @Override
    public WebRestResult uploadTeacherAvatar(MultipartFile file,String teacherid){
        User user = userMapper.selectByPrimaryKey(teacherid);
        if(user==null){
            return RespUtil.failResp("老师不存在");
        }
        String staticPath = "teacherAvatar\\"+user.getAccount() + "\\";
        String filepath = FileUtil.getAbsolutePath(staticPath);
        String filename = "avatar"+file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if( upload(file,filepath,filename).getResult()== WebRestResult.SUCCESS){
            UserPlus userPlus = userPlusMapper.selectByPrimaryKey(teacherid);
            if(userPlus == null){
                UserPlus userPlus1 = new UserPlus();
                userPlus1.setUserid(teacherid);
                userPlus1.setAvatar(FileUtil.FilePathUrl+staticPath.replace('\\','/') + filename);

                if(userPlusMapper.insert(userPlus1) == 1){
                    return RespUtil.successResp("上传老师头像成功");
                }
                return RespUtil.failResp("上传老师头像失败");
            }else{
                userPlus.setAvatar(FileUtil.FilePathUrl+staticPath.replace('\\','/') + filename);
                if(userPlusMapper.updateByPrimaryKeySelective(userPlus) == 1){
                    return RespUtil.successResp("上传老师头像成功");
                }
                return RespUtil.failResp("上传老师头像失败");
            }
        }
        return RespUtil.failResp("上传老师头像失败");
    }


    @Override
    public WebRestResult importExperiment (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);

            if(sheet != null){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    Experiment experiment = new Experiment();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ){
                        continue;
                    }
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<8;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;

                    String course = row.getCell(3).getStringCellValue();

                    experiment.setExperimentname(row.getCell(1).getStringCellValue());
                    experiment.setExperimenthour(row.getCell(2).getStringCellValue());
                    experiment.setGoal(row.getCell(4).getStringCellValue());
                    experiment.setIntro(row.getCell(5).getStringCellValue());
                    experiment.setRule(row.getCell(6).getStringCellValue());
                    experiment.setStep(row.getCell(7).getStringCellValue());

                    List<ExperimentPageResult> experimentList = experimentMapper.getExperimentList(row.getCell(1).getStringCellValue(),null,0,10,10);
                    if(experimentList.size() > 0){
                        experiment.setExperimentid(experimentList.get(0).getExperimentid());
                        experimentMapper.updateByPrimaryKeySelective(experiment);
                    }else{
                        experiment.setFlag("0");
                        experiment.setCreatedTime(new Date());
                        experiment.setExperimentid(UUIDUtil.randomUUID());
                        experimentMapper.insertSelective(experiment);
                    }
                }
                return RespUtil.successResp("子实验信息入库成功."+errorinfo);
            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public WebRestResult importStudent (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);

            if(sheet != null){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    User user = new User();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ){
                        continue;
                    }
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<5;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;
                    user.setAccount(row.getCell(1).getStringCellValue());
                    user.setUsername(row.getCell(2).getStringCellValue());
                    user.setPhone(row.getCell(4).getStringCellValue());
                    List<ClassPageResult> classlist = wlclassMapper.getWlclassList(0,10000,10000,row.getCell(3).getStringCellValue(),null);
                    if(classlist.size() == 0){
                        continue;
                    }
                    String classid = classlist.get(0).getWlclassid();
                    user.setStudentclassid(classid);

                    User tmpuser = userMapper.getUserByAccount( row.getCell(1).getStringCellValue());
                    if(tmpuser != null){
                        user.setUserid(tmpuser.getUserid());
                        userMapper.updateByPrimaryKeySelective(user);
                    }else{
                        user.setFlag("0");
                        user.setRoleid("3");
                        user.setCreatedTime(new Date());
                        user.setUserid(UUIDUtil.randomUUID());
                        userMapper.insertSelective(user);
                    }

                }
                return RespUtil.successResp("学生信息入库成功."+errorinfo);

            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public WebRestResult importTeacher (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);
            if(sheet != null  && sheet.getLastRowNum()!= 2){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    User user = new User();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ){
                        continue;
                    }
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<6;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;

                    user.setAccount(row.getCell(1).getStringCellValue());
                    user.setUsername(row.getCell(2).getStringCellValue());
                    user.setTeacherpro(row.getCell(3).getStringCellValue());
                    user.setPhone(row.getCell(4).getStringCellValue());
                    user.setTcemail(row.getCell(5).getStringCellValue());
                    User tmpuser = userMapper.getUserByAccount( row.getCell(1).getStringCellValue());
                    if(tmpuser != null){
                        user.setUserid(tmpuser.getUserid());
                        userMapper.updateByPrimaryKeySelective(user);
                    }else{
                        user.setFlag("0");
                        user.setRoleid("2");
                        user.setCreatedTime(new Date());
                        user.setUserid(UUIDUtil.randomUUID());
                        userMapper.insertSelective(user);
                    }

                }
                return RespUtil.successResp("文件数据入库成功."+errorinfo);

            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public WebRestResult importSycourse (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);
            if(sheet != null  && sheet.getLastRowNum()!= 2){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    Sycourse sycourse = new Sycourse();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ) continue;
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<8;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;

                    sycourse.setSycoursecode(row.getCell(1).getStringCellValue());
                    sycourse.setSycoursename(row.getCell(2).getStringCellValue());
                    sycourse.setSycoursehour(row.getCell(3).getStringCellValue());
                    sycourse.setCredit(row.getCell(4).getStringCellValue());
                    sycourse.setSycoursetype(row.getCell(5).getStringCellValue());
                    sycourse.setSycourseunit(row.getCell(6).getStringCellValue());
                    sycourse.setSycourseintro(row.getCell(7).getStringCellValue());
                    List<SycoursePageResult> tmpSycourseList = sycourseMapper.getSycourseList(row.getCell(1)
                            .getStringCellValue(),null,null,null,null,null,0,10,10);
                    if(tmpSycourseList.size() > 0){
                        sycourse.setSycourseid(tmpSycourseList.get(0).getSycourseid());
                        sycourseMapper.updateByPrimaryKeySelective(sycourse);
                    }else{
                        sycourse.setFlag("0");
                        sycourse.setCreatedTime(new Date());
                        sycourse.setSycourseid(UUIDUtil.randomUUID());
                        sycourseMapper.insertSelective(sycourse);
                    }
                }
                return RespUtil.successResp("文件数据入库成功."+errorinfo);
            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public WebRestResult importCourse (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);
            if(sheet != null  && sheet.getLastRowNum()!= 2){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    Course course = new Course();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ) continue;
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<6;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;

                    course.setCoursename(row.getCell(1).getStringCellValue());
                    course.setCoursecode(row.getCell(2).getStringCellValue());
                    course.setCoursetype(row.getCell(3).getStringCellValue());
                    course.setCoursesemester(row.getCell(4).getStringCellValue());
                    course.setCourseintro(row.getCell(5).getStringCellValue());
                    List<CoursePageResult> tmpCourseList = courseMapper.getCourseList(row.getCell(1)
                            .getStringCellValue(),row.getCell(2).getStringCellValue(),null,null,0,10,10);
                    if(tmpCourseList.size() > 0){
                        course.setCourseid(tmpCourseList.get(0).getCourseid());
                        courseMapper.updateByPrimaryKeySelective(course);
                    }else{
                        course.setFlag("0");
                        course.setCreatedTime(new Date());
                        course.setCourseid(UUIDUtil.randomUUID());
                        courseMapper.insertSelective(course);
                    }
                }
                return RespUtil.successResp("文件数据入库成功."+errorinfo);
            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public WebRestResult importPlatformQuestion (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);
            if(sheet != null  && sheet.getLastRowNum()!= 2){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    Question question = new Question();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ) continue;
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<8;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;

                    question.setContent(row.getCell(1).getStringCellValue());
                    question.setAchoice(row.getCell(2).getStringCellValue());
                    question.setBchoice(row.getCell(3).getStringCellValue());
                    question.setCchoice(row.getCell(4).getStringCellValue());
                    question.setDchoice(row.getCell(5).getStringCellValue());
                    String answer = row.getCell(6).getStringCellValue();
                    if(answer != "A" && answer != "B" && answer != "C" && answer != "D" ){
                        errorinfo += "第"+(line-2)+"行的答案只能时ABCD中的一个,跳过该条数据;";
                        continue;
                    }
                    question.setAnswer(answer);
                    String platformname = row.getCell(7).getStringCellValue();
                    List<PlatformPageResult> platformList  = platformMapper.getPlatformList(platformname,null,null ,null,0,10,10);
                    if(platformList.size() > 0){
                        String questionid = UUIDUtil.randomUUID();
                        question.setFlag("0");
                        question.setCreatedTime(new Date());
                        question.setQuestionid(questionid);
                        questionMapper.insertSelective(question);

                        String platformid = platformList.get(0).getPlatformid();
                        questionMapper.PlatformbindQuestion(platformid,questionid);

                    }else{
                        errorinfo += "第"+(line-2)+"行的所属平台没有匹配数据,跳过该条数据;";
                    }
                }
                return RespUtil.successResp("文件数据入库成功."+errorinfo);
            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }


    @Override
    public WebRestResult importExperimentQuestion (MultipartFile file){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);
            if(sheet != null  && sheet.getLastRowNum()!= 2){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    Question question = new Question();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ) continue;
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 1;i<8;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }else {
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                            if(row.getCell(i).getStringCellValue().isEmpty()){
                                dataIsNull = Boolean.TRUE;
                                errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                                break;
                            }
                        }
                    }
                    if(dataIsNull) continue;

                    question.setContent(row.getCell(1).getStringCellValue());
                    question.setAchoice(row.getCell(2).getStringCellValue());
                    question.setBchoice(row.getCell(3).getStringCellValue());
                    question.setCchoice(row.getCell(4).getStringCellValue());
                    question.setDchoice(row.getCell(5).getStringCellValue());
                    String answer = row.getCell(6).getStringCellValue();
                    if(answer != "A" && answer != "B" && answer != "C" && answer != "D" ){
                        errorinfo += "第"+(line-2)+"行的答案只能时ABCD中的一个,跳过该条数据;";
                        continue;
                    }
                    question.setAnswer(answer);
                    String experimentname = row.getCell(7).getStringCellValue();
                    List<ExperimentPageResult> experimentList  = experimentMapper.getExperimentList(experimentname,null,0,10,10);
                    if(experimentList.size() > 0){
                        String questionid = UUIDUtil.randomUUID();
                        question.setFlag("0");
                        question.setCreatedTime(new Date());
                        question.setQuestionid(questionid);
                        questionMapper.insertSelective(question);

                        String experimentid = experimentList.get(0).getExperimentid();
                        questionMapper.ExperimentbindQuestion(experimentid,questionid);

                    }else{
                        errorinfo += "第"+(line-2)+"行的所属实验没有匹配数据,跳过该条数据;";
                    }
                }
                return RespUtil.successResp("文件数据入库成功."+errorinfo);
            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public WebRestResult importScore (MultipartFile file,String scheduleid){
        try {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if(!suffix.equals("xlsx")){
                return RespUtil.failResp("文件格式错误");
            }
            InputStream ins = file.getInputStream();
            Workbook wb = new XSSFWorkbook(ins);
            Sheet sheet = wb.getSheetAt(0);
            if(sheet != null  && sheet.getLastRowNum()!= 2){
                String errorinfo = "";
                for(int line = 3; line <= sheet.getLastRowNum();line++){
                    ScheduleStudentGx scheduleStudentGx = new ScheduleStudentGx();
                    User user = new User();
                    Row row = sheet.getRow(line);
                    if(row == null || row.getCell(1) == null ){
                        continue;
                    }
                    Boolean dataIsNull = Boolean.FALSE;
                    for(int i = 3;i<6;i++){
                        if(row.getCell(i) == null){
                            dataIsNull = Boolean.TRUE;
                            errorinfo += "第"+(line-2)+"行" + ",第"+(i+1)+"列数据为空,跳过该条数据;";
                            break;
                        }
                        row.getCell(i).setCellType(Cell.CELL_TYPE_NUMERIC);
                    }
                    if(dataIsNull){
                        continue;
                    }
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    user = userMapper.getUserByAccount(row.getCell(1).getStringCellValue());
                    if(user == null){
                        return RespUtil.failResp("学号不存在!");
                    }
                    ScheduleStudentGxKey scheduleStudentGxKey = new ScheduleStudentGxKey(scheduleid,user.getUserid());
                    scheduleStudentGx = scheduleStudentGxMapper.selectByPrimaryKey(scheduleStudentGxKey);
                    if(scheduleStudentGx == null){
                        return RespUtil.failResp("该学生排课数据不存在");
                    }
                    scheduleStudentGx.setSygfxscore(row.getCell(3).getNumericCellValue());
                    scheduleStudentGx.setSbwhscore(row.getCell(4).getNumericCellValue());
                    scheduleStudentGx.setXczlscore(row.getCell(5).getNumericCellValue());
                    scheduleStudentGxMapper.updateByPrimaryKeySelective(scheduleStudentGx);
                    scoreService.updatePsscoreFinalscore(scheduleid,user.getUserid());
                }
                return RespUtil.successResp("文件数据入库成功."+errorinfo);

            }
            return RespUtil.failResp("文件数据为空,入库失败");
        }catch (Exception e){
            e.printStackTrace();
            return RespUtil.failResp("数据格式不规范,文件入库失败");
        }
    }

    @Override
    public void exportStudent(HttpServletResponse response, String grade, String classid){
        HSSFWorkbook wkb = new HSSFWorkbook();
        HSSFSheet sheet=wkb.createSheet("学生信息");
        HSSFRow row1=sheet.createRow(0);
        HSSFCell cell=row1.createCell(0);
        cell.setCellValue("学员考试成绩一览表");
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("学号");
        row2.createCell(1).setCellValue("姓名");
        row2.createCell(2).setCellValue("班级");
        row2.createCell(3).setCellValue("年级");
        row2.createCell(4).setCellValue("电话");
        int page = 1,pagesize = 10;
        while (true){
            int begin = (page-1) * pagesize;
            int end = pagesize;
            List<UserPageResult> userList =  userMapper.getUserList("3",null,null,grade,classid,null,null,begin,end,pagesize);
            for (int i = 0; i < userList.size(); i++) {
                UserPageResult user = userList.get(i);
                HSSFRow row3=sheet.createRow(begin + i+2);
                row3.createCell(0).setCellValue(user.getAccount());
                row3.createCell(1).setCellValue(user.getUsername());
                row3.createCell(2).setCellValue(user.getClassname());
                row3.createCell(3).setCellValue(user.getStudentgrade());
                row3.createCell(4).setCellValue(user.getPhone());
                row3.setHeightInPoints(20);
            }
            page++;
            if (userList.size() == 0){
                break;
            }
        }

        sheet.setDefaultRowHeightInPoints(20);//设置缺省行高
        sheet.setDefaultColumnWidth(25);//设置缺省列宽
        HSSFCellStyle cellStyle = wkb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cell.setCellStyle(cellStyle);
        row1.setHeightInPoints(20);
        row2.setHeightInPoints(20);

//输出Excel文件
        try {
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=student.xlsx");
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downloadPlatformQuestion(HttpServletResponse response){
        try {
            String filepath = FileUtil.getAbsolutePath("template\\安全考核题目导入（平台）.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = { "A", "B", "C", "D" };
            setXSSFValidation(sheet, textlist1, 3, 500, 6, 6);
            String[] platfromNameList = platformMapper.getAllPlatfromName();
            if(platfromNameList != null){
                setXSSFValidation(sheet, platfromNameList, 3, 500, 7, 7);
            }
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("安全考核题目导入（平台）.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    @Override
    public void downloadExperimentQuestion(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\安全考核题目导入（实验）.xlsx");
            FileInputStream input = new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = {"A", "B", "C", "D"};
            setXSSFValidation(sheet, textlist1, 3, 500, 6, 6);
            String[] experimentNameList = experimentMapper.getAllExperimentName();
            if (experimentNameList != null) {
                String[] experimentNameList2 = {experimentNameList[0], experimentNameList[1], experimentNameList[2], experimentNameList[3]};

                setXSSFValidation(sheet, experimentNameList2, 3, 500, 7, 7);
            }
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("安全考核题目导入（实验）.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void downloadScore(HttpServletResponse response,String scheduleid) {
        List<StudentScorePageResult> schedule = scheduleStudentGxMapper.getStudentScoreBySchedule(scheduleid, null, null, null, 0, 100000, 10000);

        try {
            String filepath = FileUtil.getAbsolutePath("template\\成绩导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            XSSFCellStyle cellStyle = wkb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
            for (int i = 0; i < schedule.size(); i++) {
                XSSFRow row = sheet.createRow(i + 3);
//                XSSFRow row = sheet.getRow(i + 3);
                row.createCell(0);
                row.createCell(1);
                row.createCell(2);

                XSSFCell cell = row.getCell(0);
                int num = i+1;
                cell.setCellValue(String.valueOf(num));
                cell.setCellStyle(cellStyle);

                cell = row.getCell(1);
                cell.setCellValue(schedule.get(i).getAccount());
                cell.setCellStyle(cellStyle);

                cell = row.getCell(2);
                cell.setCellValue(schedule.get(i).getUsername());
                cell.setCellStyle(cellStyle);
                row.setHeightInPoints(20);
            }
            sheet.setDefaultRowHeightInPoints(20);//设置缺省行高
            sheet.setDefaultColumnWidth(25);//设置缺省列宽

            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("成绩导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downloadTeacher(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\老师信息导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = { "教授/教授级高工", "副教授/高级工程师", "讲师/工程师", "助理教授/助理工程师" };
            setXSSFValidation(sheet, textlist1, 3, 500, 3, 3);
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("老师信息导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downloadllCourse(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\理论课程信息导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = { "学类核心", "专业核心", "专业选修" };
            setXSSFValidation(sheet, textlist1, 2, 500, 3, 3);
            String[] textlist2 = { "第1学期", "第2学期", "第3学期", "第4学期", "第5学期", "第6学期", "第7学期", "第8学期" };
            setXSSFValidation(sheet, textlist2, 2, 500, 4, 4);
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("理论课程信息导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downloadgxExperiment(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\实验课程或平台与子实验关系导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = platformMapper.getAllPlatfromName();
            String[] textlist2 = sycourseMapper.getAllCourseName();
            String[] textlist3 = Arrays.copyOf(textlist1, textlist1.length + textlist2.length);
            System.arraycopy(textlist2, 0, textlist3, textlist1.length, textlist2.length);
            if(textlist3 != null) {
                setXSSFValidation(sheet, textlist3, 2, 500, 1, 1);
            }
            String[] experimentNameList = experimentMapper.getAllExperimentName();
            if(experimentNameList != null){
                setXSSFValidation(sheet, experimentNameList, 2, 500, 2, 2);
            }
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("实验课程或平台与子实验关系导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    @Override
    public void downloadsyCourse(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\实验课程信息导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = { "学类核心", "专业核心", "专业选修" };
            setXSSFValidation(sheet, textlist1, 2, 500, 5, 5);
            String[] schoolNameList = schoolMapper.getAllSchoolName();
            if(schoolNameList != null){
                setXSSFValidation(sheet, schoolNameList, 2, 500, 6, 6);
            }
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("实验课程信息导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downloadStudent(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\学生信息导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = { "班长", "学习委员", "无" };
            setXSSFValidation(sheet, textlist1, 2, 500, 5, 5);
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("学生信息导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downloadExperiment(HttpServletResponse response) {
        try {
            String filepath = FileUtil.getAbsolutePath("template\\子实验信息导入.xlsx");
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wkb = new XSSFWorkbook(input);
            XSSFSheet sheet = wkb.getSheetAt(0);
            String[] textlist1 = courseMapper.getAllCourseName();
            if (textlist1 != null) {
                setXSSFValidation(sheet, textlist1, 2, 500, 3, 3);
            }
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("子实验信息导入.xlsx", "utf-8"));
            response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static XSSFSheet setXSSFValidation(XSSFSheet sheet,String[] textlist, int firstRow,
                                              int endRow, int firstCol,int endCol) {

        CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(textlist);
        DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
        //处理Excel兼容性问题
        if (createValidation instanceof XSSFDataValidation) {
            createValidation.setSuppressDropDownArrow(true);
            createValidation.setShowErrorBox(true);
        } else {
            createValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(createValidation);
        return sheet;
    }

}

